package com.qjs.smterp.utils;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.TreeMap;

public class DbUtils {
    private static final int WRAP_ALL_FOUND_COLUMNS = 0;
    private static final int SKIP_REDUNDANT_COLUMNS = 1;
    public static String VENDOR = "";
    public static final String ORACLE_VENDOR = "ORACLE";
    public static String JAVA_DATE_TIME_PATTERN = "yyyy/MM/dd HH:mm:ss";

    /**
     * <p>
     * Convert ResultSet to ArrayList of TreeMaps. Keyset of TreeMap is based on
     * column names of source ResultSet
     * <p>
     * Note: TreeMap should be replaced by HashMap if no bugs shows up during
     * test.
     * 
     * @param source
     *            <code>ResultSet</code> source to be converted
     * @return ArrayList containing ResultSet data in ArrayList:TreeMap form
     */
    public static ArrayList<TreeMap<String, Object>> resultToArray(
	    ResultSet source) {
	if (source == null) {
	    return null;
	}
	ResultSetMetaData meta;
	ArrayList<TreeMap<String, Object>> dest = new ArrayList<TreeMap<String, Object>>();
	try {
	    meta = source.getMetaData();
	    String[] columnNames = new String[meta.getColumnCount()];

	    for (int i = 0; i < meta.getColumnCount(); i++) {
		columnNames[i] = meta.getColumnName(i + 1);
	    }

	    while (source.next()) {
		TreeMap<String, Object> row = new TreeMap<String, Object>();
		for (int i = 0; i < meta.getColumnCount(); i++) {
		    row.put(columnNames[i], source.getObject(i + 1));
		}
		dest.add(row);
	    }
	    source.close();
	} catch (SQLException e) {
	    e.printStackTrace();
	}
	return dest;
    }

    /**
     * <p>
     * Creates ArrayList filled with javabeans. Data source is ResultSet passed.
     * Each bean is filled using it's setter methods.
     * <p>
     * To successfully complete, bean must have at least one setter for each
     * ResultSet column. Setters are case insensitively matched (except of "set"
     * part of it's name) against column names (e.g. for column "AttRiBute"
     * corresponding setter may be "setAtTribuTe").
     * <p>
     * Setter must have only one parameter of type corresponding to sql type of
     * ResultSet column.
     * 
     * @param source
     *            <code>ResultSet</code> source to be converted
     * @param destClass
     *            <code>Class</code>, class of javabean to represent each row of
     *            <b>source</b>
     * @return <code>ArrayList</code> filled with data from ResultSet
     * @throws Exception
     *             thrown on several cases: <li>no setter found for column in
     *             <b>source<b> <li>no situable setter found for column in
     *             <b>source<b> <li>SQL datatype of any column wasn't recognized
     */
    @SuppressWarnings("rawtypes")
    public static ArrayList resultToClassArray(ResultSet source, Class destClass)
	    throws Exception {
	return resultToClassArrayHelper(source, destClass,
		WRAP_ALL_FOUND_COLUMNS);
    }

    /**
     * <p>
     * Creates ArrayList filled with javabeans. Data source is ResultSet passed.
     * Each bean is filled using it's setter methods.
     * <p>
     * To successfully complete, bean must have at least one setter for each
     * ResultSet column. Setters are case insensitively matched (except of "set"
     * part of it's name) against column names (e.g. for column "AttRiBute"
     * corresponding setter may be "setAtTribuTe").
     * <p>
     * Setter must have only one parameter of type corresponding to sql type of
     * ResultSet column.
     * 
     * @param source
     *            <code>ResultSet</code> source to be converted
     * @param destClass
     *            <code>Class</code>, class of javabean to represent each row of
     *            <b>source</b>
     * @return <code>ArrayList</code> filled with data from ResultSet
     * @throws Exception
     *             thrown on several cases: <li>no situable setter found for
     *             column in <b>source<b> <li>SQL datatype of any column wasn't
     *             recognized
     */
    @SuppressWarnings("rawtypes")
    public static ArrayList resultToClassArraySkipRedundantColumns(
	    ResultSet source, Class destClass) throws Exception {
	return resultToClassArrayHelper(source, destClass,
		SKIP_REDUNDANT_COLUMNS);
    }

    @SuppressWarnings({ "rawtypes", "unchecked" })
    private static ArrayList resultToClassArrayHelper(ResultSet source,
	    Class destClass, int skipRedundantColumns) throws Exception {
	int i;

	if (source == null) {
	    return null;
	}

	ResultSetMetaData meta;
	ArrayList dest = new ArrayList();
	try {
	    meta = source.getMetaData();
	    String[] columnNames = new String[meta.getColumnCount()];
	    int[] columnTypes = new int[meta.getColumnCount()];
	    Method[] classMethods = destClass.getMethods();
	    TreeMap allMethods = new TreeMap();
	    TreeMap methods = new TreeMap();
	    for (i = 0; i < classMethods.length; i++) {
		allMethods.put(classMethods[i].getName().toLowerCase(),
			classMethods[i]);
	    }

	    for (i = 0; i < meta.getColumnCount(); i++) {
		columnNames[i] = meta.getColumnName(i + 1);

		if (allMethods.get("set" + columnNames[i].toLowerCase()) == null) {
		    if (skipRedundantColumns != SKIP_REDUNDANT_COLUMNS) {
			throw new Exception(
				"Destination class doesn't contain setter method for column: "
					+ columnNames[i]);
		    }
		} else {
		    Method mt = (Method) allMethods.get("set"
			    + columnNames[i].toLowerCase());
		    methods.put("set" + columnNames[i].toLowerCase(), mt);
		    Class param = mt.getParameterTypes()[0];
		    if (param.isAssignableFrom(String.class)) {
			columnTypes[i] = DbUtilsSupport
				.getJavaTypeFromSQLType(Types.VARCHAR); 
		    } else {
			columnTypes[i] = DbUtilsSupport
				.getJavaTypeFromSQLType(meta
					.getColumnType(i + 1));
		    }
		}
	    }

	    Iterator methodIter = methods.keySet().iterator();
	    while (methodIter.hasNext()) {
		Object key = methodIter.next();
		Method setter = (Method) methods.get(key);
		Class[] params = setter.getParameterTypes();
		if (params.length != 1) {
		    throw new Exception(
			    "Failed to find situable parameters for setter: "
				    + (String) key);
		}
	    }

	    while (source.next()) {
		Object row = destClass.newInstance();
		for (i = 0; i < meta.getColumnCount(); i++) {
		    Method lMethod = (Method) methods.get("set"
			    + columnNames[i].toLowerCase());
		    if (lMethod != null) {
			Object[] param = { DbUtilsSupport.getObjectOnJAVA_TYPE(
				columnTypes[i], source, i + 1) };
			lMethod.invoke(row, param);
		    } else {
			if (skipRedundantColumns != SKIP_REDUNDANT_COLUMNS) {
			    throw new Exception(
				    "Destination class doesn't contain setter method for column: "
					    + columnNames[i]);
			}
		    }
		}
		dest.add(row);
	    }
	    source.close();
	} catch (SQLException e) {
	    e.printStackTrace();
	}
	return dest;
    }

    @SuppressWarnings({ "rawtypes", "unchecked" })
    public static String appendFilter(Object dataSrc, List oDataList,
	    String table) throws IllegalArgumentException,
	    InvocationTargetException {
	if (dataSrc == null) {
	    return "";
	}
	Class dataCls = dataSrc.getClass();
	Method[] mth = dataCls.getMethods();
	String ret = "";
	String op;
	try {
	    for (int i = 0; i < mth.length; i++) {
		if (!mth[i].getName().equals("getClass")
			&& mth[i].getName().substring(0, 3).equals("get")) {
		    Object data = mth[i].invoke(dataSrc, new Object[]{ null});
		    if (data == null || ((String) data).equals("")) {
			continue;
		    }

		    if (((String) data).indexOf("%") != -1
			    || ((String) data).indexOf("_") != -1) {
			op = " LIKE ";
		    } else {
			op = " = ";
		    }

		    ret += " AND " + table + mth[i].getName().substring(3) + op
			    + "?";
		    oDataList.add(data);
		}
	    }
	} catch (IllegalAccessException e) {
	    e.printStackTrace();
	}
	return ret;
    }

    public static <T> StringBuilder appendFilter(StringBuilder sb,
	    String logicOperator, String operator, String columnName, T x) {
	sb.append(logicOperator).append(" ").append(columnName).append(" ");
	if (null == x) {
	    sb.append("IS NULL");
	} else {
	    if (x instanceof String) {
		String s = x.toString();
		if (s.length() == 0) {
		    sb.append("IS NULL");
		} else {
		    s = s.replace("'", "''");
		    sb.append(operator).append(" '").append(s).append("'");
		}
	    } else {
		sb.append(operator).append(" ").append(x);
	    }
	}
	return sb.append(" ");
    }

    public static Boolean getBoolean(ResultSet rs, String columnName)
	    throws SQLException {
	boolean val = rs.getBoolean(columnName);
	return rs.wasNull() ? Boolean.FALSE : Boolean.valueOf(val);
    }

    public static Boolean getBoolean(ResultSet rs, int columnIndex)
	    throws SQLException {
	boolean val = rs.getBoolean(columnIndex);
	return rs.wasNull() ? Boolean.FALSE : Boolean.valueOf(val);
    }

    public static Boolean getBoolean(ResultSet rs, String columnName,
	    String asTrue, String asFalse) throws SQLException {
	return compare(rs.getString(columnName), asTrue, asFalse);
    }

    public static Boolean getBoolean(ResultSet rs, int columnIndex,
	    String asTrue, String asFalse) throws SQLException {
	return compare(rs.getString(columnIndex), asTrue, asFalse);
    }

    public static Byte getByte(ResultSet rs, String columnName)
	    throws SQLException {
	byte val = rs.getByte(columnName);
	return rs.wasNull() ? null : Byte.valueOf(val);
    }

    public static Byte getByte(ResultSet rs, int columnIndex)
	    throws SQLException {
	byte val = rs.getByte(columnIndex);
	return rs.wasNull() ? null : Byte.valueOf(val);
    }

    public static Short getShort(ResultSet rs, String columnName)
	    throws SQLException {
	short val = rs.getShort(columnName);
	return rs.wasNull() ? null : Short.valueOf(val);
    }

    public static Short getShort(ResultSet rs, int columnIndex)
	    throws SQLException {
	short val = rs.getShort(columnIndex);
	return rs.wasNull() ? null : Short.valueOf(val);
    }

    public static Integer getInteger(ResultSet rs, int columnIndex)
	    throws SQLException {
	int val = rs.getInt(columnIndex);
	return rs.wasNull() ? null : Integer.valueOf(val);
    }

    public static Integer getInteger(ResultSet rs, String columnName)
	    throws SQLException {
	int val = rs.getInt(columnName);
	return rs.wasNull() ? null : Integer.valueOf(val);
    }

    public static Long getLong(ResultSet rs, int columnIndex)
	    throws SQLException {
	long val = rs.getLong(columnIndex);
	return rs.wasNull() ? null : Long.valueOf(val);
    }

    public static Long getLong(ResultSet rs, String columnName)
	    throws SQLException {
	long val = rs.getLong(columnName);
	return rs.wasNull() ? null : Long.valueOf(val);
    }

    public static Double getDouble(ResultSet rs, int columnIndex)
	    throws SQLException {
	double val = rs.getDouble(columnIndex);
	return rs.wasNull() ? null : Double.valueOf(val);
    }

    public static Double getDouble(ResultSet rs, String columnName)
	    throws SQLException {
	double val = rs.getDouble(columnName);
	return rs.wasNull() ? null : Double.valueOf(val);
    }

    public static BigDecimal getBigDecimal(ResultSet rs, int columnIndex,
	    int shiftLeft) throws SQLException {
	BigDecimal val = rs.getBigDecimal(columnIndex);
	if (val != null && shiftLeft != 0) {
	    val = val.movePointLeft(shiftLeft);
	}
	return val;
    }

    public static BigDecimal getBigDecimal(ResultSet rs, String columnName,
	    int shiftLeft) throws SQLException {
	BigDecimal val = rs.getBigDecimal(columnName);
	if (val != null && shiftLeft != 0) {
	    val = val.movePointLeft(shiftLeft);
	}
	return val;
    }

    public static java.util.Date getDate(ResultSet rs, String columnName)
	    throws SQLException {
	return getDate(rs.getDate(columnName));
    }

    public static java.util.Date getDate(ResultSet rs, int columnIndex)
	    throws SQLException {
	return getDate(rs.getDate(columnIndex));
    }

    public static java.util.Date getTime(ResultSet rs, String columnName)
	    throws SQLException {
	return getTime(rs.getTime(columnName));
    }

    public static java.util.Date getTime(ResultSet rs, int columnIndex)
	    throws SQLException {
	return getTime(rs.getTime(columnIndex));
    }

    public static java.util.Date getTimestamp(ResultSet rs, String columnName)
	    throws SQLException {
	return getTimestamp(rs.getTimestamp(columnName));
    }

    public static java.util.Date getTimestamp(ResultSet rs, int columnIndex)
	    throws SQLException {
	return getTimestamp(rs.getTimestamp(columnIndex));
    }

    public static java.util.Date getDate(java.sql.Date date) {
	return (date == null) ? null : new java.util.Date(date.getTime());
    }

    public static java.util.Date getTime(java.sql.Time time) {
	return (time == null) ? null : new java.util.Date(time.getTime());
    }

    public static java.util.Date getTimestamp(java.sql.Timestamp timestamp) {
	return (timestamp == null) ? null : new java.util.Date(
		timestamp.getTime());
    }

    public static java.sql.Date getSqlDate(java.util.Date date) {
	return (date == null) ? null : new java.sql.Date(date.getTime());
    }

    public static java.sql.Time getSqlTime(java.util.Date time) {
	return (time == null) ? null : new java.sql.Time(time.getTime());
    }

    public static java.sql.Timestamp getSqlTimestamp(java.util.Date timestamp) {
	return (timestamp == null) ? null : new java.sql.Timestamp(
		timestamp.getTime());
    }

    public static String getNotNull(ResultSet rs, int columnIndex)
	    throws SQLException {
	String s = rs.getString(columnIndex);
	return (s == null) ? "" : s;
    }

    public static <T extends Number> void setNumber(PreparedStatement cstmt,
	    int parameterIndex, T x) throws SQLException {
	if (x == null) {
	    cstmt.setNull(parameterIndex, java.sql.Types.NUMERIC);
	} else if (x instanceof Double) {
	    cstmt.setDouble(parameterIndex, x.doubleValue());
	} else if (x instanceof Long) {
	    cstmt.setLong(parameterIndex, x.longValue());
	} else if (x instanceof Integer) {
	    cstmt.setInt(parameterIndex, x.intValue());
	} else if (x instanceof Short) {
	    cstmt.setShort(parameterIndex, x.shortValue());
	} else if (x instanceof Byte) {
	    cstmt.setShort(parameterIndex, x.byteValue());
	} else {
	    cstmt.setObject(parameterIndex, x);
	}
    }

    public static void setString(CallableStatement cstmt, int parameterIndex,
	    String str) throws SQLException {
	if (str == null || str.isEmpty()) {
	    cstmt.setNull(parameterIndex, java.sql.Types.VARCHAR);
	} else {
	    cstmt.setString(parameterIndex, str);
	}
    }

    public static <T extends Number> void setNumber(CallableStatement cstmt,
	    String parameterName, T x) throws SQLException {
	if (x == null) {
	    cstmt.setNull(parameterName, java.sql.Types.NUMERIC);
	} else if (x instanceof Double) {
	    cstmt.setDouble(parameterName, x.doubleValue());
	} else if (x instanceof Long) {
	    cstmt.setLong(parameterName, x.longValue());
	} else if (x instanceof Integer) {
	    cstmt.setInt(parameterName, x.intValue());
	} else if (x instanceof Short) {
	    cstmt.setShort(parameterName, x.shortValue());
	} else if (x instanceof Byte) {
	    cstmt.setShort(parameterName, x.byteValue());
	} else {
	    cstmt.setObject(parameterName, x);
	}
    }

    public static void setNumber(PreparedStatement cstmt, int parameterIndex,
	    Boolean x, String... values) throws SQLException {
	if (x == null) {
	    cstmt.setNull(parameterIndex, java.sql.Types.NUMERIC);
	} else if (values.length == 0) {
	    cstmt.setBoolean(parameterIndex, x.booleanValue());
	} else if (values.length < 2) {
	    throw new SQLException("Too many values for Boolean parameter "
		    + parameterIndex);
	} else {
	    cstmt.setString(parameterIndex, values[x.booleanValue() ? 0 : 1]);
	}
    }

    public static void setNumber(CallableStatement cstmt, String parameterName,
	    Boolean x, String... values) throws SQLException {
	if (x == null) {
	    cstmt.setNull(parameterName, java.sql.Types.NUMERIC);
	} else if (values.length == 0) {
	    cstmt.setBoolean(parameterName, x.booleanValue());
	} else if (values.length < 2) {
	    throw new SQLException("Too many values for Boolean parameter "
		    + parameterName);
	} else {
	    cstmt.setString(parameterName, values[x.booleanValue() ? 0 : 1]);
	}
    }

    private static Boolean compare(String val, String asTrue, String asFalse) {
	// We state on the next purposes:
	// 1. if val == asTrue then true
	// 2. if val == asFalse then false
	// 3. else null
	if (null == val) {
	    if (null == asTrue) {
		return Boolean.TRUE;
	    } else if (null == asFalse) {
		return Boolean.FALSE;
	    }
	} else if (val.equals(asTrue)) {
	    return Boolean.TRUE;
	} else if (val.equals(asFalse)) {
	    return Boolean.FALSE;
	}
	return null;
    }

    public static void setValueInStatement(PreparedStatement ps, int index,
	    Object value) throws SQLException {
	if (value == null) {
	    ps.setString(index, null);
	} else if (value instanceof Date) {
	    ps.setTimestamp(index,
		    new java.sql.Timestamp(((Date) value).getTime()));
	} else if (value instanceof Integer) {
	    ps.setInt(index, (Integer) value);
	} else if (value instanceof Long) {
	    ps.setLong(index, (Long) value);
	} else if (value instanceof Float) {
	    ps.setFloat(index, (Float) value);
	} else if (value instanceof Double) {
	    ps.setDouble(index, (Double) value);
	} else if (value instanceof Boolean) {
	    if ((Boolean) value) {
		ps.setInt(index, 1);
	    } else {
		ps.setInt(index, 0);
	    }
	} else {
	    ps.setString(index, value.toString());
	}
    }

    public static void setValueInCallableStatement(CallableStatement cs,
	    int index, Object value) throws SQLException {
	setValueInCallableStatement(cs, index, false, value);
    }

    public static void setValueInCallableStatement(CallableStatement cs,
	    int index, boolean isDateAsString, Object value)
	    throws SQLException {
	if (value == null) {
	    cs.setNull(index, 0);
	} else if (value instanceof Date) {
	    if (isDateAsString) {
		cs.setString(index,
			new SimpleDateFormat(JAVA_DATE_TIME_PATTERN)
				.format((Date) value));
	    } else {
		cs.setTimestamp(index,
			new java.sql.Timestamp(((Date) value).getTime()));
	    }
	} else if (value instanceof Integer) {
	    cs.setInt(index, (Integer) value);
	} else if (value instanceof Long) {
	    cs.setLong(index, (Long) value);
	} else if (value instanceof Float) {
	    cs.setFloat(index, (Float) value);
	} else if (value instanceof Double) {
	    cs.setDouble(index, (Double) value);
	} else if (value instanceof Boolean) {
	    if ((Boolean) value) {
		cs.setInt(index, 1);
	    } else {
		cs.setInt(index, 0);
	    }
	} else {
	    cs.setString(index, value.toString());
	}
    }

    // public static int getDbVendorCursor() {
    // return
    // ApplicationParameters.DB_VENDOR.getValue().equals(DbUtils.ORACLE_VENDOR)
    // ?
    // oracle.jdbc.OracleTypes.CURSOR :
    // com.ibm.db2.jcc.DB2Types.CURSOR;
    // }
}

final class DbUtilsSupport {
    private final static int JAVA_STRING = 1;
    private final static int JAVA_BIGDECIMAL = 2;
    private final static int JAVA_BOOLEAN = 3;
    private final static int JAVA_INTEGER = 4;
    private final static int JAVA_LONG = 5;
    private final static int JAVA_FLOAT = 6;
    private final static int JAVA_DOUBLE = 7;
    private final static int JAVA_BYTEAR = 8;
    private final static int JAVA_DATE = 9;
    private final static int JAVA_TIME = 10;
    private final static int JAVA_TIMESTAMP = 11;
    private final static int JAVA_UNKNOWN = 200;

    /**
     * Return corresponding java type for sql type. Currently supported: <li>
     * String</li> <li>BigDecimal</li> <li>Boolean</li> <li>Integer</li> <li>
     * Long</li> <li>Float</li> <li>Double</li> <li>Byte[] (?)</li> <li>
     * java.sql.Date</li> <li>java.sql.Time</li> <li>java.sql.Timestamp</li> <li>
     * Object</li>
     * 
     * @param sqlType
     *            <code>int</code> java.sqp.Types constant
     * @return <code>int</code> corresponding java type constant
     */
    public static int getJavaTypeFromSQLType(int sqlType) {
	switch (sqlType) {
	case Types.CHAR:
	case Types.VARCHAR:
	case Types.LONGVARCHAR:
	    return JAVA_STRING;

	case Types.NUMERIC:
	case Types.DECIMAL:
	    return JAVA_BIGDECIMAL;

	case Types.BIT:
	    return JAVA_BOOLEAN;

	case Types.TINYINT:
	case Types.SMALLINT:
	case Types.INTEGER:
	    return JAVA_INTEGER;

	case Types.BIGINT:
	    return JAVA_LONG;

	case Types.REAL:
	    return JAVA_FLOAT;

	case Types.FLOAT:
	case Types.DOUBLE:
	    return JAVA_DOUBLE;

	case Types.BINARY:
	case Types.VARBINARY:
	case Types.LONGVARBINARY:
	    return JAVA_BYTEAR;

	case Types.DATE:
	    return JAVA_DATE;

	case Types.TIME:
	    return JAVA_TIME;

	case Types.TIMESTAMP:
	    return JAVA_TIMESTAMP;

	default:
	    return JAVA_UNKNOWN;
	}
    }

    /**
     * <p>
     * Gets data from column of ResultSet, calling right getter, based on java
     * type of column.
     * <p>
     * Result of getting column data automaticaly upcaste to Object.
     * 
     * @param javaType
     *            <code>int</code> Java type source column must have
     * @param source
     *            <code>ResultSet</code> data source
     * @param colInd
     *            <code>int</code> column index
     * @return <code>Object</code> result of getting data
     * @throws Exception
     *             , thrown if java type wasn't recognized
     */
    public static Object getObjectOnJAVA_TYPE(int javaType, ResultSet source,
	    int colInd) throws Exception {
	switch (javaType) {
	case JAVA_STRING:
	    return source.getString(colInd);
	    // case JAVA_BIGDECIMAL : return source.getBigDecimal( colInd );
	case JAVA_BOOLEAN:
	    return Boolean.valueOf(source.getBoolean(colInd));
	case JAVA_INTEGER:
	    return Integer.valueOf(source.getInt(colInd));
	case JAVA_LONG:
	    return Long.valueOf(source.getLong(colInd));
	case JAVA_FLOAT:
	    return Float.valueOf(source.getFloat(colInd));
	case JAVA_BIGDECIMAL:
	case JAVA_DOUBLE:
	    return Double.valueOf(source.getDouble(colInd));
	case JAVA_DATE:
	    return source.getDate(colInd);
	case JAVA_TIME:
	    return source.getTime(colInd);
	case JAVA_TIMESTAMP:
	    return source.getTimestamp(colInd);
	case JAVA_BYTEAR:
	case JAVA_UNKNOWN:
	    return source.getObject(colInd);
	default:
	    throw new Exception(
		    "Unknown SQL column type for name (or not implemented): "
			    + source.getMetaData().getColumnName(colInd));
	}
    }
}
